Refresh Method Example

This example uses the Refresh method to update the Fields collection of the Categories table based on changes to the OrdinalPosition data. The order of the Fields in the collection changes only after the Refresh method is used.

Sub RefreshX()

   Dim dbsNorthwind As Database
   Dim tdfEmployees As TableDef
   Dim aintPosition() As Integer
   Dim astrFieldName() As String
   Dim intTemp As Integer
   Dim fldLoop As Field

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set tdfEmployees = dbsNorthwind.TableDefs("Categories")

   With tdfEmployees
      ' Display original OrdinalPosition data and store it
      ' in an array.
      Debug.Print _
         "Original OrdinalPosition data in TableDef."
      ReDim aintPosition(0 To .Fields.Count - 1) As Integer
      ReDim astrFieldName(0 To .Fields.Count - 1) As String
      For intTemp = 0 To .Fields.Count - 1
         aintPosition(intTemp) = _
            .Fields(intTemp).OrdinalPosition
         astrFieldName(intTemp) = .Fields(intTemp).Name
         Debug.Print , aintPosition(intTemp), _
            astrFieldName(intTemp)
      Next intTemp

      ' Change OrdinalPosition data.
      For Each fldLoop In .Fields
         fldLoop.OrdinalPosition = _
            100 - fldLoop.OrdinalPosition
      Next fldLoop
      Set fldLoop = Nothing

      ' Print new data.
      Debug.Print "New OrdinalPosition data before Refresh."
      For Each fldLoop In .Fields
         Debug.Print , fldLoop.OrdinalPosition, fldLoop.Name
      Next fldLoop

      .Fields.Refresh

      ' Print new data, showing how the field order has been 
      ' changed.
      Debug.Print "New OrdinalPosition data after Refresh."
      For Each fldLoop In .Fields
         Debug.Print , fldLoop.OrdinalPosition, fldLoop.Name
      Next fldLoop

      ' Restore original OrdinalPosition data.
      For intTemp = 0 To .Fields.Count - 1
         .Fields(astrFieldName(intTemp)).OrdinalPosition = _
            aintPosition(intTemp)
      Next intTemp
   End With

   dbsNorthwind.Close

End Sub